﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Mvc;
using System.Data;
using MySql.Data.MySqlClient;

using BMS.Models;
using BMS.Utilities;

namespace BMS.Models.DataAccess
{
    public class FMRepository
    {
        private DatabaseAccess db = new DatabaseAccess();

        public List<FMModel> GetAllFMs()
        {
            List<FMModel> allFMs = new List<FMModel>();
            String query = "SELECT * FROM " + Configuration.DatabaseName + ".fm";

            db.DBConnectionOn();

            try
            {
                MySqlCommand cmd = new MySqlCommand(query, db.connection);
                MySqlDataReader dataReader = cmd.ExecuteReader();

                while (dataReader.Read())
                {
                    FMModel fm = new FMModel();

                    fm.FMID = int.Parse(dataReader["in_fldFMID"] + "");
                    fm.FMCode = dataReader["vc_fldFMCode"] + "";
                    fm.FMName = dataReader["vc_fldFMName"] + "";
                    fm.AttentionTo = dataReader["vc_fldAttentionTo"] + "";
                    fm.CompanyName = dataReader["vc_fldCompanyName"] + "";
                    fm.FMCreationDate = DateTime.Parse(dataReader["dt_fldCreate"] + "");

                    allFMs.Add(fm);
                }
            }
            catch (Exception e)
            {
                throw;
            }
            finally
            {
                db.DBConnectionOff();
            }
            return allFMs;
        }

        public FMModel GetFM(String FMName)
        {
            FMModel fm = new FMModel();

            List<FMModel> fmList = GetAllFMs();

            foreach (FMModel item in fmList)
            {
                if (item.FMName == FMName)
                {
                    fm = item;
                    break;
                }
            }

            return fm;
        }

        public bool IsFMFound(String FMName)
        {
            bool isFound = false;

            List<FMModel> fmList = GetAllFMs();

            foreach(FMModel fm in fmList)
            {
                if(fm.FMName == FMName)
                {
                    isFound = true;
                    break;
                }
            }

            return isFound;
        }

        public bool Insert(FMModel fmModel)
        {
            bool isInserted = false;

            try
            {
                String query = "INSERT INTO " + Configuration.DatabaseName + ".fm (vc_fldFMCode, vc_fldFMName, vc_fldAttentionTo, vc_fldCompanyName, dt_fldCreate) VALUES('" + fmModel.FMCode + "', '"
                    + fmModel.FMName + "', '" + fmModel.AttentionTo + "', '" + fmModel.CompanyName + "', '" + fmModel.FMCreationDate.ToString("yyyy-MM-dd H:mm:ss") + "')";
                db.DBConnectionOn();
                //create command and assign the query and connection from the constructor
                MySqlCommand cmd = new MySqlCommand(query, db.connection);

                //Execute command
                cmd.ExecuteNonQuery();

                isInserted = true;
            }
            catch (Exception e)
            {
                isInserted = false;
                throw;
            }
            finally
            {
                db.DBConnectionOff();
            }

            return isInserted;
        }

        public IEnumerable<SelectListItem> GetFMsForDropDownList(List<FMModel> modelList, String selectedFM)
        {
            List<SelectListItem> FMList = new List<SelectListItem>();

            foreach (FMModel FM in modelList)
            {
                SelectListItem sListItem = new SelectListItem();
                sListItem.Text = FM.FMName;
                sListItem.Value = FM.FMName;
                sListItem.Selected = false;

                if (sListItem.Value == selectedFM)
                    sListItem.Selected = true;

                FMList.Add(sListItem);
            }

            return FMList;
        }

        public IEnumerable<SelectListItem> GetFMsForDropDownList(List<FMModel> modelList, bool isFilter = false)
        {
            List<SelectListItem> FMList = new List<SelectListItem>();

            if(isFilter == true)
            {
                SelectListItem tempItem = new SelectListItem();
                tempItem.Text = "All";
                tempItem.Value = "All";
                tempItem.Selected = true;

                FMList.Add(tempItem);
            }

            foreach (FMModel FM in modelList)
            {
                SelectListItem sListItem = new SelectListItem();
                sListItem.Text = FM.FMName;
                sListItem.Value = FM.FMName;

                FMList.Add(sListItem);
            }

            return FMList;
        }
    }
}